/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.NhaCungCap;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Kim
 */
public class NhaCungCapDao {

	public static ArrayList<NhaCungCap> layDanhSach() {
		ArrayList<NhaCungCap> ds = new ArrayList<NhaCungCap>();
		String sql = "SELECT  *  FROM  NhaCungCap ";

		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MANCC");
				String ten = rs.getString("TenNCC");
				String dc = rs.getString("DiaChi");
				String dt = rs.getString("DienThoai");
				String em = rs.getString("Email");
				NhaCungCap tmp = new NhaCungCap(ma, ten, dc, dt, em);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static ArrayList<NhaCungCap> timKiemByTen(String tenNCC) {
		ArrayList<NhaCungCap> ds = new ArrayList<NhaCungCap>();

		String sql = "SELECT * FROM NhaCungCap WHERE tenNCC LIKE '%" + tenNCC + "%'";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("MANCC");
				String ten = rs.getString("TenNCC");
				String dc = rs.getString("DiaChi");
				String dt = rs.getString("DienThoai");
				String em = rs.getString("Email");
				NhaCungCap tmp = new NhaCungCap(ma, ten, dc, dt, em);
				ds.add(tmp);
			}
		} catch (Exception ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}
		return ds;
	}

	public static boolean them(NhaCungCap ncc) {
		boolean kq = false;
		String sql = "INSERT INTO  NhaCungCap  ( MaNCC  , TenNCC  , DiaChi  , DienThoai  , Email ) VALUES ";

		String s = String.format("( %d, N'%s', N'%s', N'%s', N'%s'   )", ncc.getMaNCC(), ncc.getTenNCC(), ncc.getDiaChi(), ncc.getDienThoai(), ncc.geteMail());
		sql += s;
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean xoa(int maNCC) {
		boolean kq = false;
		String str = "if not exists (select kn.mancc from KhaNangCungUng kn where kn.mancc =" + maNCC + " )"
			 + " and  not exists (select pn.MaNCC from PNhapKho pn where pn.MaNCC = " + maNCC + ")";
		String sql = String.format(str + "DELETE FROM NhaCungCap WHERE MaNCC=%d", maNCC);
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static boolean capNhat(NhaCungCap ncc) {
		boolean kq = false;
		String sql = String.format("UPDATE NhaCungCap "
			 + "SET "
			 + "TenNCC = N'%s', "
			 + "DiaChi = N'%s', "
			 + "DienThoai = N'%s', "
			 + "Email = N'%s' "
			 + "WHERE  MaNCC = %d; ", ncc.getTenNCC(), ncc.getDiaChi(), ncc.getDienThoai(), ncc.geteMail(), ncc.getMaNCC());
		KetNoi cn = null;
		int n;
		n = -1;
		try {
			cn = new KetNoi();
			n = cn.thucThi(sql);
		} catch (SQLException ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);
		}
		if (n == 1) {
			kq = true;
		}
		cn.dongKetNoi();
		return kq;
	}

	public static NhaCungCap layByMa(int maNCC) {
		NhaCungCap tmp = null;
		try {
			String sql = "SELECT * FROM NhaCungCap WHERE MaNCC =" + maNCC;
			KetNoi helper = new KetNoi();
			ResultSet rs = helper.docBang(sql);
			if (rs.next()) {
				int ma = rs.getInt("MANCC");
				String ten = rs.getString("TenNCC");
				String dc = rs.getString("DiaChi");
				String dt = rs.getString("DienThoai");
				String em = rs.getString("Email");
				tmp = new NhaCungCap(ma, ten, dc, dt, em);
			}
			helper.dongKetNoi();
		} catch (Exception ex) {
			System.out.println(ex.getMessage());
		}
		return tmp;
	}

	public int layMaMax() {
		int kq = 0;
		String sql = "Select Max(mancc) as max from nhacungcap";
		KetNoi cn = null;
		ResultSet rs = null;
		try {
			cn = new KetNoi();
			rs = cn.docBang(sql);
			while (rs.next()) {
				int ma = rs.getInt("max");
				//return ma;
				kq = ma;
			}
		} catch (Exception ex) {
			Logger.getLogger(NhaCungCapDao.class.getName()).log(Level.SEVERE, null, ex);

		} finally {
			cn.dongKetNoi();
		}

		return kq;
	}
}
